/*
 *  Copyright 2020-2025 the original author or authors.
 *  You cannot use this file unless authorized by the author.
 */

package org.ipig.commons.helper;

import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.ipig.constants.SymbolCnst;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

/**
 * ConnectionHelper
 *
 * @author <a href="mailto:comchnts@163.com">chinats</a>
 * @version $$Id$$
 * @since 1.0
 */
@Slf4j
public class ConnectionHelper {
    static String driverClass;
    static String url;
    private static Connection conn = null;
    private static Properties props = new Properties();
    private static String configProps = "config.properties";

    static {
        PropertiesHelper propsHelper = null;
        try {
            InputStream is = ClassLoaderHelper.getResourceAsStream(configProps, ConnectionHelper.class);
            if (is != null) {
                props.load(is);
                props.put("remarksReporting", "true");
                propsHelper = new PropertiesHelper(props);
            }
        } catch (Exception ex) {
            log.error(ex.getMessage());
        }
        if (propsHelper != null) {
            driverClass = propsHelper.get("driverClassName", driverClass);
            url = propsHelper.get("url", url);
            props.put("user", propsHelper.get("user"));
            props.put("password", propsHelper.get("password"));
        }
    }

    /**
     * getConnection
     *
     * @return Connection
     */
    public static Connection getConnection() {
        try {
            if (conn == null || conn.isClosed()) {
                synchronized (ConnectionHelper.class) {
                    if (conn == null || conn.isClosed()) {
                        //首先建立驱动
                        Class.forName(driverClass);
                        //驱动成功后进行连接
                        conn = DriverManager.getConnection(url, props);
                        log.warn("连接成功");
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn; //返回一个连接
    }


    /**
     * 获取getPrimaryKeys主键信息
     *
     * @param conn    Connection
     * @param catalog 数据库名
     * @param schema  模式名
     * @param table   表名
     * @return JSONObject
     */
    public static JSONObject getPrimaryKeys(Connection conn, String catalog, String schema, String table) {
        ResultSet pkRs = null;
        StringBuilder sb = new StringBuilder();
        JSONObject pk = new JSONObject();
        AssertHelper.notNull(conn, "Connection为null");
        AssertHelper.hasText(catalog, "catalog必填");
        AssertHelper.hasText(schema, "schema必填");
        AssertHelper.hasText(table, "table必填");
        try {
            DatabaseMetaData dbmd = conn.getMetaData();
            pkRs = dbmd.getPrimaryKeys(catalog, schema, table);
            JSONObject primaryKeys = new JSONObject(4);
            boolean flag = true;
            while (pkRs.next()) {
                if (flag) {
                    String tableName = pkRs.getString("TABLE_NAME");  //表名
                    pk.put("TABLE_CAT", pkRs.getString("TABLE_CAT"));  //表类别(可为null)
                    pk.put("TABLE_SCHEM", pkRs.getString("TABLE_SCHEM"));//表模式（可能为空）,在oracle中获取的是命名空间,其它数据库未知
                    pk.put("TABLE_NAME", tableName);
                    pk.put("PK_NAME", pkRs.getString("PK_NAME")); //主键名称
                    flag = false;
                }
                short keySeq = pkRs.getShort("KEY_SEQ");//序列号(主键内值1表示第一列的主键，值2代表主键内的第二列)
                String pkColName = pkRs.getString("COLUMN_NAME");//列名
                sb.append(pkColName).append(SymbolCnst.COMMA);
            }
            if (sb.length() > 0) {
                sb = sb.deleteCharAt(sb.length() - 1);
            }
            pk.put("primaryKeys", sb.toString());
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            ConnectionHelper.release(pkRs);
        }
        return pk;
    }

    /**
     * 获取table表信息
     *
     * @param conn    Connection
     * @param catalog 数据库名
     * @param schema  模式名
     * @param table   表名
     * @return Map<String, JSONObject>
     */
    public static Map<String, JSONObject> getTables(Connection conn, String catalog, String schema, String table) {
        ResultSet tabRs = null;
        Map<String, JSONObject> tabs = new HashMap<>();
        AssertHelper.notNull(conn, "Connection为null");
        AssertHelper.hasText(catalog, "catalog必填");
        AssertHelper.hasText(schema, "schema必填");
        AssertHelper.hasText(table, "table必填");
        try {
            DatabaseMetaData dbmd = conn.getMetaData();
            tabRs = dbmd.getTables(catalog, schema, table, new String[]{"TABLE"});
            while (tabRs.next()) {
                JSONObject tab = new JSONObject();
                String tableName = StringUtils.trimToEmpty(tabRs.getString("TABLE_NAME"));  //表名
                tab.put("TABLE_CAT", StringUtils.trimToEmpty(tabRs.getString("TABLE_CAT")));  //表类别(可为null)
                tab.put("TABLE_SCHEM", StringUtils.trimToEmpty(tabRs.getString("TABLE_SCHEM")));//表模式（可能为空）,在oracle中获取的是命名空间,其它数据库未知
                tab.put("TABLE_NAME", tableName);
                tab.put("TABLE_TYPE", StringUtils.trimToEmpty(tabRs.getString("TABLE_TYPE")));  //表类型。典型的类型是 "TABLE"、"VIEW"、"SYSTEM TABLE"、"GLOBAL TEMPORARY"、"LOCAL TEMPORARY"、"ALIAS" 和 "SYNONYM"。
                tab.put("REMARKS", StringUtils.trimToEmpty(tabRs.getString("REMARKS")));  //表的注释
                tabs.put(tableName, tab);
                log.info(tab.toJSONString());
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            ConnectionHelper.release(tabRs);
        }
        return tabs;
    }

    public static void release(ResultSet... resultSetArgs) {
        release(null, null, resultSetArgs);
    }

    public static void release(Statement... statementArgs) {
        if (statementArgs != null && statementArgs.length > 0) {
            for (int i = 0; i < statementArgs.length; i++) {
                Statement st = statementArgs[i];
                try {
                    if (st != null && !st.isClosed()) {
                        st.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void release(Connection connection) {
        release(connection, null, null);
    }

    public static void release(Statement statement, ResultSet... resultSetArgs) {
        release(null, statement, resultSetArgs);
    }

    public static void release(Statement statement, ResultSet tabRs) {
        release(null, statement, null);
    }

    public static void release(Connection connection, Statement statement, ResultSet... resultSetArgs) {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (statement != null && !statement.isClosed()) {
                statement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (resultSetArgs != null && resultSetArgs.length > 0) {
            for (int i = 0; i < resultSetArgs.length; i++) {
                ResultSet rs = resultSetArgs[i];
                try {
                    if (rs != null && !rs.isClosed()) {
                        rs.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
